package cn.conac.rc.itm.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import cn.conac.rc.framework.repository.GenericDao;
import cn.conac.rc.itm.entity.AelItemDeploiedEntity;

@Repository
public interface AelItemDeploiedRepository extends GenericDao<AelItemDeploiedEntity, Integer>{

    @Query(value = "SELECT DISTINCT BEAN.DUTY_CONTENT FROM AEL_ITEMS_DUTY CT LEFT JOIN OFS_DUTY BEAN ON CT.DUTY_CODE=BEAN.DUTY_CODE LEFT JOIN OFS_ORGANIZATION OO ON OO.BASE_ID = BEAN.ORG_ID WHERE CT.ITEM_ID = :itemId AND OO.ID = :orgId", nativeQuery = true)
    List<String> findDutyContents(@Param("itemId") Integer itemId, @Param("orgId") String orgId);
    
    @Query(value = "SELECT BEAN.GENERAL_NAME from AEL_GENERAL_ITEM BEAN where BEAN.GENERAL_CODE = :generalCode", nativeQuery = true)
    String findGeneralName(@Param("generalCode") String generalCode);

    @Query(value = "SELECT base.name from ofs_organization org, ofs_organization_base base where org.base_id = base.id and org.id = :orgId", nativeQuery = true)
    String findOrganizationName(@Param("orgId") String orgId);

    @Query(value = "SELECT BEAN.OBJECT_IM_IDS from AEL_ITEMS_INFO BEAN where BEAN.id = :itemId", nativeQuery = true)
    String findObjectImIds(@Param("itemId") Integer itemId);
    
    @Query(value = "SELECT BEAN.name from ICP_DICTIONARY BEAN where BEAN.id = :objectIm and BEAN.type = 'objectIm'", nativeQuery = true)
    String findObjectImName(@Param("objectIm") String objectIm);
    
    @Query(value = "SELECT BEAN.DEPT_TYPE from AEL_ITEMS_INFO BEAN where BEAN.id = :itemId", nativeQuery = true)
    String findDeptType(@Param("itemId") Integer itemId);
    
    @Query(value = "SELECT BEAN.DEPT_NAME from OFS_DEPARTMENT BEAN where BEAN.id = :deptId", nativeQuery = true)
    String findDeptName(@Param("deptId") String deptId);
    
    @Query(value = "select distinct bean.dept_name from ael_items_dept ct left join ofs_department bean on ct.dept_code=bean.dep_code left join ofs_organization oo on oo.base_id = bean.org_id where ct.item_id=:itemId and oo.id=:orgId", nativeQuery = true)
    List<String> findDeptContents(@Param("itemId") Integer itemId, @Param("orgId") String orgId);
    
    @Query(value = "select oob.NAME from ael_items_org aio,ofs_organization oo,ofs_organization_base oob where aio.ITEM_ID = :itemId and aio.ORG_ID = oo.ID and oo.BASE_ID = oob.ID", nativeQuery = true)
    List<String> findOrganizations(@Param("itemId") Integer itemId);

    @Query(value = "select count(aid.ID) from AEL_ITEMS_CATALOGUE aic, AEL_ITEMS_DEPLOIED aid where aic.ID = aid.MAIN_ITEMS and aic.LEVELS = '1' and aic.ITEM_TYPE <> '1' and aid.ITEM_TYPE <> '1' and aic.AREA_ID = :areaId", nativeQuery = true)
    long findChildAelItemCnt(@Param("areaId") String areaId);
    
}
